"""
Case Type   : Compatibility
Case Name   : 在自定义函数、存储过程中创建prepare语句
Create At   : 2024/10/22
Owner       : liu-tong-8848
Description :
              1.在自定义函数中创建prepare语句
              2.在存储过程中创建prepare语句
              3.调用函数后再执行prepare语句
              4.调用存储过程后再执行prepare语句
              5.清理环境
Expect      :
              1.成功
              2.成功
              3.成功
              4.成功
              5.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.db_user_node = Node(node='PrimaryDbBmode')

    def test_add_dimension(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        step1 = '--step1:在自定义函数中创建prepare语句;expect:成功'
        self.log.info(step1)
        sql = "drop function if exists f_prepare_009(); CREATE OR REPLACE FUNCTION f_prepare_009(i integer) " \
              + "RETURNS integer AS \$\$ BEGIN prepare test_prepare_009 from 'select 1'; RETURN i + 1; END;" \
              + " \$\$ LANGUAGE plpgsql;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step1)
        self.assertIn(self.Constant.CREATE_FUNCTION_SUCCESS_MSG, result, "执行失败" + step1)
        self.assertIn(self.Constant.DROP_FUNCTION_SUCCESS_MSG, result, "执行失败" + step1)

        step2 = '--step2:在存储过程中创建prepare语句;expect:成功'
        self.log.info(step2)
        sql = " drop procedure if exists p_prepare_009(); CREATE OR REPLACE PROCEDURE p_prepare_009() " \
              + "AS BEGIN prepare test_prepare_009_1 from 'select 1'; END;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step2)
        self.assertIn(self.Constant.CREATE_PROCEDURE_SUCCESS_MSG, result, "执行失败" + step2)
        self.assertIn(self.Constant.DROP_PROCEDURE_SUCCESS_MSG, result, "执行失败" + step2)

        step3 = '--step3:调用函数后再执行prepare语句;expect:成功'
        self.log.info(step3)
        sql = "select f_prepare_009(1); execute test_prepare_009;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step3)

        step4 = '--step4:调用存储过程后再执行prepare语句;expect:合理报错'
        self.log.info(step4)
        sql = "call p_prepare_009(); execute test_prepare_009_1; "
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step4)

    def tearDown(self):
        step5 = 'step5:清理环境 expect:清理环境成功'
        self.log.info(step5)
        clean_sql = "drop function if exists t_prepare_009(); drop procedure if exists p_prepare_009(); " \
                    + "deallocate prepare test_prepare_009; deallocate prepare test_prepare_009_1;"
        clean_result = self.primary_sh.execut_db_sql(clean_sql)
        self.assertIn(self.Constant.DROP_FUNCTION_SUCCESS_MSG, clean_result, "执行失败" + step5)
        self.assertIn(self.Constant.DROP_PROCEDURE_SUCCESS_MSG, clean_result, "执行失败" + step5)
        self.log.info(clean_result)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
